package com.css.fxfzxczh.modules.eqDisasterLossAssess.houseEconomicLossAssess.repository.impl;

import com.css.fxfzxczh.modules.eqDisasterLossAssess.houseEconomicLossAssess.entity.*;
import com.css.fxfzxczh.modules.eqDisasterLossAssess.houseEconomicLossAssess.repository.HouseEconomicLossAssessRepository;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.util.List;


@Repository
public class HouseEconomicLossAssessRepositoryImpl implements HouseEconomicLossAssessRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public void deleteHouseFailureRatio(String xsemId) {
        String sql = "delete from xczh_house_failure_ratio where xsem_id = ?";
        jdbcTemplate.update(sql,xsemId);
    }

    @Override
    public void saveHouseFailureRatio(HouseFailureRatioEntity houseFailureRatioEntity) {
        String sql = "insert into xczh_house_failure_ratio values(:id,:xsemId,:xstmId,:evaluationSubareaName,:structureType,:intactRatio,:minorRatio,:moderateRatio,:seriousRatio,:destroyRatio,:delFlag,:createUser,:createTime,:updateUser,:updateTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(houseFailureRatioEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<HouseFailureRatioEntity> getHouseFailureRatioByXsemId(String xsemId) {
        String sql = "select * from xczh_house_failure_ratio where xsem_id = '"+xsemId+"' and del_flag = '0' ";
        List<HouseFailureRatioEntity> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseFailureRatioEntity.class));
        return list;
    }


    @Override
    public List<HouseFailureRatioEntity> getHouseFailureRatioByXstmId(String xstmId) {
        String sql = "select * from xczh_house_failure_ratio where xstm_id = '"+xstmId+"' and del_flag = '0' order by create_time";
        List<HouseFailureRatioEntity> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseFailureRatioEntity.class));
        return list;
    }


    @Override
    public boolean save(HouseLossRatioEntity houseLossRatioEntity) {
        String sql = "insert into xczh_house_loss_ratio values(:id,:xsemId,:intact1,:minor1,:moderate1,:serious1,:destroy1,:intact2,:minor2,:moderate2,:serious2,:destroy2,:intact3,:minor3,:moderate3,:serious3,:destroy3,:intact4,:minor4,:moderate4,:serious4,:destroy4,:delFlag,:createUser,:createTime,:updateUser,:updateTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(houseLossRatioEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public boolean update(HouseLossRatioEntity houseLossRatioEntity) {
        String sql = "update xczh_house_loss_ratio set xsem_id=:xsemId,intact1=:intact1,minor1=:minor1,moderate1=:moderate1,serious1=:serious1,destroy1=:destroy1," +
                "intact2=:intact2,minor2=:minor2,moderate2=:moderate2,serious2=:serious2,destroy2=:destroy2," +
                "intact3=:intact3,minor3=:minor3,moderate3=:moderate3,serious3=:serious3,destroy3=:destroy3," +
                "intact4=:intact4,minor4=:minor4,moderate4=:moderate4,serious4=:serious4,destroy4=:destroy4," +
                "del_flag=:delFlag,create_user=:createUser,create_time=:createTime,update_user=:updateUser,update_time=:updateTime where id = '"+houseLossRatioEntity.getId()+"'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(houseLossRatioEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public HouseLossRatioEntity getHouseLossRatio(String xsemId) {
        String sql = "select * from xczh_house_loss_ratio where del_flag = '0' and xsem_id = '"+xsemId+"'";
        try {
            HouseLossRatioEntity houseLossRatioEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HouseLossRatioEntity.class));
            return houseLossRatioEntity;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public List<HousesDirectEconomicLosses> getHousesDirectEconomicLossesByXstmId(String xstmId) {
        String sql = "select * from xczh_houses_direct_economic_losses where xstm_id='"+xstmId+"' and del_flag='0'";
        List<HousesDirectEconomicLosses> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HousesDirectEconomicLosses.class));
        return list;
    }

    @Override
    public void saveHousesDirectEconomicLosses(HousesDirectEconomicLosses housesDirectEconomicLosses) {
        String sql = "insert into xczh_houses_direct_economic_losses values(:id,:xsemId,:xstmId,:evaluationSubareaName,:structureType,:intactValue,:minorValue,:moderateValue,:seriousValue,:destroyValue,:totalLoss,:delFlag,:createUser,:createTime,:updateUser,:updateTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(housesDirectEconomicLosses);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public String getSumTotal(String xstmId) {
        try {
            String sql = "select sum(total_loss) from xczh_houses_direct_economic_losses where  xstm_id='"+xstmId+"' and del_flag='0'  group by xstm_id ";
            return jdbcTemplate.queryForObject(sql, String.class);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public boolean saveDisasterAreasHousesDirectEconomicLosses(DisasterAreasHousesDirectEconomicLosses disasterAreasHousesDirectEconomicLosses) {
        String sql = "insert into xczh_disaster_areas_houses_direct_economic_losses values(:id,:xsemId,:xstmId,:evaluationSubareaName,:housesLosses,:delFlag,:createUser,:createTime,:updateUser,:updateTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(disasterAreasHousesDirectEconomicLosses);
        return namedParameterJdbcTemplate.update(sql, source)>0;
    }

    @Override
    public List<DisasterAreasHousesDirectEconomicLosses> getDisasterAreasHousesDirectEconomicLossesByXsemId(String xsemId) {
        String sql = "select * from xczh_disaster_areas_houses_direct_economic_losses where xsem_id='"+xsemId+"' and del_flag='0'";
        List<DisasterAreasHousesDirectEconomicLosses> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterAreasHousesDirectEconomicLosses.class));
        return list;
    }

    @Override
    public DisasterAreasHousesDirectEconomicLosses getDisasterAreasHousesByXstmId(String xstmId) {
        String sql = "select * from xczh_disaster_areas_houses_direct_economic_losses where xstm_id='"+xstmId+"' and del_flag='0'";
        try {
            DisasterAreasHousesDirectEconomicLosses disasterAreasHousesDirectEconomicLosses = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DisasterAreasHousesDirectEconomicLosses.class));
            return disasterAreasHousesDirectEconomicLosses;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public void deleteDisasterAreasHousesDirectEconomicLosses(String xsemId) {
        String sql = "delete from xczh_disaster_areas_houses_direct_economic_losses where xsem_id = ? ";
        jdbcTemplate.update(sql,xsemId);
    }

    @Override
    public boolean saveAdministrationAreaHousesDirectEconomicLosses(AdministrationAreaHousesDirectEconomicLosses entity) {
        String sql = "insert into xczh_administration_area_houses_direct_economic_losses values(:id,:xsemId,:province,:city,:county,:ruralHouse,:ruralShared,:cityHouse,:cityShared,:governmentOffice,:educationSystem,:healthSystem,:otherUsage,:totalLoss,:delFlag,:createUser,:createTime,:updateUser,:updateTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source)>0;
    }

    @Override
    public List<AdministrationAreaHousesDirectEconomicLosses> getAdministrationData(String xsemId) {
        String sql = "select * from xczh_administration_area_houses_direct_economic_losses where xsem_id = '"+xsemId+"' and del_flag = '0' order by create_time";
        List<AdministrationAreaHousesDirectEconomicLosses> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AdministrationAreaHousesDirectEconomicLosses.class));
        return list;
    }

    @Override
    public AdministrationAreaHousesDirectEconomicLosses getAdministrationDataByXsemIdAndCounty(String xsemId, String province, String city, String county) {
        String sql = "select sum(rural_house) ruralHouse,sum(rural_shared) ruralShared,sum(city_house) cityHouse,sum(city_shared) cityShared,sum(government_office) governmentOffice,sum(education_system) educationSystem,sum(health_system) healthSystem " +
                "from xczh_administration_area_houses_direct_economic_losses where xsem_id = '"+xsemId+"' and province='"+province+"' and city='"+city+"' and county = '"+county+"' and del_flag = '0' " +
                "group by province,city,county";
        try {
            AdministrationAreaHousesDirectEconomicLosses administrationAreaHousesDirectEconomicLosses = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(AdministrationAreaHousesDirectEconomicLosses.class));
            return administrationAreaHousesDirectEconomicLosses;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public void delHousesDirectEconomicLossesByXsemId(String xstmId) {
        String sql = "delete from xczh_houses_direct_economic_losses where xsem_id = ? ";
        jdbcTemplate.update(sql,xstmId);
    }

    @Override
    public void batchSave(List<HousesDirectEconomicLosses> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "insert into xczh_houses_direct_economic_losses values(:id,:xsemId,:xstmId,:evaluationSubareaName,:structureType,:intactValue," +
                ":minorValue,:moderateValue,:seriousValue,:destroyValue,:totalLoss,:delFlag,:createUser,:createTime,:updateUser,:updateTime)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);

    }

    @Override
    public void delAdministrationAreaHousesDirectEconomicLosses(String xsemId) {
        String sql = "delete from xczh_administration_area_houses_direct_economic_losses where xsem_id = ? ";
        jdbcTemplate.update(sql,xsemId);
    }

    @Override
    public List<AdministrationAreaHousesDirectEconomicLosses> getLossesByXsemId(String xsemId) {
        String sql = "select * from xczh_administration_area_houses_direct_economic_losses where xsem_id = '"+xsemId+"' and del_flag = '0' order by create_time";
        List<AdministrationAreaHousesDirectEconomicLosses> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AdministrationAreaHousesDirectEconomicLosses.class));
        return list;
    }

}
